package com.example.jxl;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 找出 麦客报名表.xls & 百度手机助手名单.xls 共同用户，写入 麦客-百度_105.xls
 */
public class mai_baidu_105 {

	public static void main(String[] args) throws IOException {
		File file0 = new File("C:/Users/Administrator/Desktop/麦客报名表.xls");
		File file1 = new File("C:/Users/Administrator/Desktop/百度手机助手名单.xls");
		File fileOutput = new File(
				"C:/Users/Administrator/Desktop/麦客-百度_120.xls");

		Map<Integer, Integer> map = new HashMap<Integer, Integer>();
		Map<Integer, String> nickMap = new HashMap<Integer, String>();

		// 百度手机助手名单.xlsx
		try {
			Workbook book = Workbook.getWorkbook(file1);

			Sheet sheet = book.getSheet(0);

			int rows = sheet.getRows();

			for (int i = 1; i < rows; i++) {
				Cell cell = sheet.getCell(0, i);

				Integer uid = Integer.valueOf(cell.getContents());

				map.put(uid, uid);
				nickMap.put(uid, sheet.getCell(1, i).getContents());
			}

			System.out.println("百度手机助手名单.xlsx 读取完");

			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		List<String[]> list = new ArrayList<String[]>();

		// 麦客报名表.xls
		try {
			Workbook book = Workbook.getWorkbook(file0);
			Sheet sheet = book.getSheet(0);

			int rows = sheet.getRows();

			for (int col = 2; col < rows - 4; col++) {
				try {
					Cell cell = sheet.getCell(3, col);

					Integer uid = Integer.valueOf(cell.getContents());

					if (map.containsKey(uid)) {
						Cell[] row = sheet.getRow(col);

						// 增加字段
						List<String> cellList = toStringList(row);
						cellList.add(4, nickMap.get(uid));

						list.add(cellList.toArray(new String[0]));
					}
				} catch (Exception e) {
				}
			}

			System.out.println("麦客报名表.xls 读取完");

			book.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		System.out.println(list.size());

		// 筛选
		List<String[]> listFilter = new ArrayList<String[]>();

		Random random = new Random(123);

		while (listFilter.size() < 125) {
			int index = random.nextInt(list.size());

			String[] row = list.get(index);

			list.remove(index);
			listFilter.add(row);
		}

		// 按编号排序
		Collections.sort(listFilter, new Comparator<String[]>() {

			public int compare(String[] o1, String[] o2) {
				String s0 = o1[0];
				String s1 = o2[0];

				Integer id0 = Integer.valueOf(s0);
				Integer id1 = Integer.valueOf(s1);

				return id0 - id1;
			}
		});

		System.out.println("筛选完毕：" + listFilter.size());

		// 筛选结果写入xls
		try {
			WritableWorkbook wbook = Workbook.createWorkbook(fileOutput);
			WritableSheet wsheet = wbook.createSheet("sheet0", 0);

			for (int row = 0; row < listFilter.size(); row++) {

				String[] contentArr = listFilter.get(row);

				for (int column = 0; column < contentArr.length; column++) {
					String content = contentArr[column];

					wsheet.addCell(new Label(column, row, content));
				}
			}

			wbook.write();// 写入Excel对象
			wbook.close();// 关闭可写入的Excel对象
		} catch (Exception e) {
			e.printStackTrace();
		}

		System.out.println("完成");
	}

	public static List<Cell> toList(Cell[] cells) {
		List<Cell> list = new ArrayList<Cell>();

		for (Cell cell : cells) {
			list.add(cell);
		}

		return list;
	}

	public static List<String> toStringList(Cell[] cells) {
		List<String> list = new ArrayList<String>();

		for (Cell cell : cells) {
			list.add(cell.getContents());
		}

		return list;
	}

}
